<cfcomponent extends="AbsDAO" output="false">

	<cffunction name="create" access="public" returntype="Numeric" output="false">
		<cfargument name="post" type="it.millemultimedia.easyBlog.model.Post" required="true" />	
		<cfset var qry = "" />	
		<cfset var id = 0 />
		
		<cftransaction>
		
			<cfquery name="qry" datasource="#getDataSource().getName()#" username="#getDataSource().getUserName()#" password="#getDataSource().getPassword()#">
				INSERT INTO Post
				(
					authorid,status,publishingdate,uuid,
				title,urlsafe,maincontent,excerpt,allowComment
				)
				VALUES
				(
					<cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#arguments.post.getauthorId()#" null="false" />,
				<cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#arguments.post.getstatus()#" null="false" />,
				<cfqueryparam cfsqltype="CF_SQL_TIMESTAMP" value="#arguments.post.getpublishingDate()#" null="false" />,
				<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#arguments.post.getuuid()#" null="false" />,
				<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#arguments.post.gettitle()#" null="false" />,
				<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#arguments.post.geturlSafe()#" null="false" />,
				<cfqueryparam cfsqltype="CF_SQL_LONGVARCHAR" value="#arguments.post.getmainContent()#" null="false" />,
				<cfqueryparam cfsqltype="CF_SQL_LONGVARCHAR" value="#arguments.post.getexcerpt()#" null="false" />,
				<cfqueryparam cfsqltype="CF_SQL_BIT" value="#arguments.post.getAllowComment()#" null="false" />
				)
			</cfquery>
			
			<cfquery name="qry" datasource="#getDataSource().getName()#" username="#getDataSource().getUserName()#" password="#getDataSource().getPassword()#">
			SELECT MAX(idpost) AS id
			FROM Post
			</cfquery>
			
			<cfset id = qry.id />
			
			<cfset arguments.post.setIdPost( id )/>
			
			<cfset setCategories( arguments.post )/>
		
		</cftransaction>

		<cfreturn id />
	</cffunction>
	
	<cffunction name="read" access="public" returntype="Query" output="false">
		<cfargument name="post" type="it.millemultimedia.easyBlog.model.Post" required="true" />
		<cfargument name="adminMode" type="boolean" required="false" default="false" />
		<cfset var qry="" />
		
		<cfquery name="qry" datasource="#getDataSource().getName()#" username="#getDataSource().getUserName()#" password="#getDataSource().getPassword()#">
			SELECT	COUNT(Comment.idPost) AS commentCount, Post.idPost, Post.authorId, 
					Post.status, Post.allowComment, Post.publishingDate, Post.uuid, 
                    Post.title, Post.urlSafe, Post.mainContent, Post.excerpt, "User".name
			FROM    Post INNER JOIN
            		"User" ON Post.authorId = "User".idUser LEFT OUTER JOIN
            		Comment ON Post.idPost = Comment.idPost 
					<cfif arguments.adminMode eq false>
					AND Comment.status IN (1)
					</cfif>
					
			GROUP BY	Post.idPost, Post.authorId, Post.status, Post.allowComment, 
						Post.publishingDate, Post.uuid, Post.title, Post.urlSafe, 
			            Post.mainContent, Post.excerpt, "User".name
						
			HAVING Post.idpost = <cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#arguments.post.getidPost()#" null="false" />
			
			<cfif arguments.adminMode eq false>
			AND post.publishingDate <= #now()#
			</cfif>
		</cfquery>
		
		<cfreturn qry />
	</cffunction>
	
	<cffunction name="update" access="public" returntype="void" output="false">
		<cfargument name="post" type="it.millemultimedia.easyBlog.model.Post" required="true" />
		
		<cfset var qry="" />
		
		<cftransaction>

			<cfquery name="qry" datasource="#getDataSource().getName()#" username="#getDataSource().getUserName()#" password="#getDataSource().getPassword()#">
				UPDATE Post
				SET status = <cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#arguments.post.getstatus()#" null="false" />,
					publishingdate = <cfqueryparam cfsqltype="CF_SQL_TIMESTAMP" value="#arguments.post.getpublishingDate()#" null="false" />,
					title = <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#arguments.post.gettitle()#" null="false" />,
					maincontent = <cfqueryparam cfsqltype="CF_SQL_LONGVARCHAR" value="#arguments.post.getmainContent()#" null="false" />,
					excerpt = <cfqueryparam cfsqltype="CF_SQL_LONGVARCHAR" value="#arguments.post.getexcerpt()#" null="false" />,
					allowComment = <cfqueryparam cfsqltype="CF_SQL_BIT" value="#arguments.post.getAllowComment()#" null="false" />
				WHERE idpost = <cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#arguments.post.getidPost()#" null="false" />
			</cfquery>
			
			<cfset setCategories( arguments.post )/>

		</cftransaction>

	</cffunction>
	
	<cffunction name="delete" access="public" returntype="void" output="false">
		<cfargument name="post" type="it.millemultimedia.easyBlog.model.Post" required="true" />
		
		<cfset var qry="" />

		<cfquery name="qry" datasource="#getDataSource().getName()#" username="#getDataSource().getUserName()#" password="#getDataSource().getPassword()#">
			DELETE FROM Post
			WHERE idpost = <cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#arguments.post.getidPost()#" null="false" />
		</cfquery>
	</cffunction>
	
	<cffunction name="list" access="public" returntype="Query" output="false">
		<cfargument name="post" type="it.millemultimedia.easyBlog.model.Post" required="true" />
		<cfargument name="adminMode" type="boolean" required="false" default="false" />
		<cfset var qry="" />

		<cfquery name="qry" datasource="#getDataSource().getName()#" username="#getDataSource().getUserName()#" password="#getDataSource().getPassword()#">
			SELECT	COUNT(Comment.idPost) AS commentCount, Post.idPost, Post.authorId, 
					Post.status, Post.allowComment, Post.publishingDate, Post.uuid, 
                    Post.title, Post.urlSafe, Post.mainContent, Post.excerpt, "User".name
			FROM    Post INNER JOIN
            		"User" ON Post.authorId = "User".idUser LEFT OUTER JOIN
            		Comment ON Post.idPost = Comment.idPost 
					<cfif arguments.adminMode eq false>
					AND Comment.status IN (1)
					</cfif>
					
			GROUP BY	Post.idPost, Post.authorId, Post.status, Post.allowComment, 
						Post.publishingDate, Post.uuid, Post.title, Post.urlSafe, 
			            Post.mainContent, Post.excerpt, "User".name
						
			HAVING Post.idPost is NOT NULL 
			
			<cfif !isNull( arguments.post.getidPost() ) >
			AND  Post.idPost = <cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#arguments.post.getidPost()#" null="false" />
			</cfif>
			
				
			<cfif !isNull( arguments.post.getauthorId() ) >
			AND  Post.authorId = <cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#arguments.post.getauthorId()#" null="false" />
			</cfif>
			
				
			<cfif !isNull( arguments.post.getstatus() ) >
			AND  Post.status = <cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#arguments.post.getstatus()#" null="false" />
			</cfif>
			
				
			<cfif !isNull( arguments.post.getpublishingDate() ) >
			AND  Post.publishingDate = <cfqueryparam cfsqltype="CF_SQL_TIMESTAMP" value="#arguments.post.getpublishingDate()#" null="false" />
			</cfif>
			
				
			<cfif !isNull( arguments.post.getuuid() ) >
			AND  Post.uuid = <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#arguments.post.getuuid()#" null="false" />
			</cfif>
			
				
			<cfif !isNull( arguments.post.gettitle() ) >
			AND  Post.title = <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#arguments.post.gettitle()#" null="false" />
			</cfif>
			
				
			<cfif !isNull( arguments.post.geturlSafe() ) >
			AND  Post.urlSafe = <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#arguments.post.geturlSafe()#" null="false" />
			</cfif>
			
				
			<cfif !isNull( arguments.post.getmainContent() ) >
			AND  Post.mainContent = <cfqueryparam cfsqltype="CF_SQL_LONGVARCHAR" value="#arguments.post.getmainContent()#" null="false" />
			</cfif>
			
				
			<cfif !isNull( arguments.post.getexcerpt() ) >
			AND  Post.excerpt = <cfqueryparam cfsqltype="CF_SQL_LONGVARCHAR" value="#arguments.post.getexcerpt()#" null="false" />
			</cfif>
			
			<cfif arguments.adminMode eq false>
			AND post.publishingDate <= #now()#
			</cfif>
			
			ORDER BY publishingDate DESC
		</cfquery>
		
		<cfreturn qry/>
	</cffunction>
	
	<cffunction name="getCategories" access="public" returntype="Query">
		<cfargument name="idPost" type="String" required="true" hint="List of id" />
		
		<cfset var qry = "" />
		
		<cfquery name="qry" datasource="#getDataSource().getName()#" username="#getDataSource().getUserName()#" password="#getDataSource().getPassword()#">
			SELECT	Category.idCategory, Category.name, Category.description, Category.uuid, Post_Category.idPost
			FROM    Post_Category INNER JOIN
              		Category ON Post_Category.idCategory = Category.idCategory
			WHERE   Post_Category.idPost IN( #arguments.idPost# )
		</cfquery>
		
		<cfreturn qry />
			
	</cffunction>
	
	<cffunction name="getPostCount" access="public" returntype="Query">
		<cfargument name="adminMode" type="boolean" required="false" default="false" />
		
		<cfset var qry = "" />
		
		<cfquery name="qry" datasource="#getDataSource().getName()#" username="#getDataSource().getUserName()#" password="#getDataSource().getPassword()#">
			SELECT      COUNT(post.idPost) AS totalPost, YEAR(post.publishingDate) AS year, 
						MONTH(post.publishingDate) AS month
			FROM     	post
			<cfif arguments.adminMode eq false>
			WHERE post.publishingDate <= #now()#
			AND post.status = 1
			</cfif>
			GROUP BY month(post.publishingDate), year(post.publishingDate)
			ORDER BY year(post.publishingDate) DESC, month(post.publishingDate)  DESC
		</cfquery>
		
		<cfreturn qry />
			
	</cffunction>
	
	<cffunction name="setCategories" access="private" returntype="void">
		<cfargument name="post" type="it.millemultimedia.easyBlog.model.Post" required="true" />
		
		<cfset var qry = "" />
		<cfset var next = "" />
		<cfset var iterator = arguments.post.getCategories().iterator() />
		
		<cftransaction>
		
			<cftry>
			
				<cfquery name="qry" datasource="#getDataSource().getName()#" username="#getDataSource().getUserName()#" password="#getDataSource().getPassword()#">
					DELETE Post_Category
					WHERE idPost = <cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#arguments.post.getIdPost()#" null="false" />
				</cfquery>
				
				<cfloop condition="#iterator.hasNext()#">
				
					<cfset next = iterator.next() />
			
					<cfquery name="qry" datasource="#getDataSource().getName()#" username="#getDataSource().getUserName()#" password="#getDataSource().getPassword()#">
						INSERT INTO Post_Category
						(
							idPost,idCategory
						)
						VALUES
						(
						<cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#arguments.post.getIdPost()#" null="false" />,
						<cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#next.getIdCategory()#" null="false" />
						)
					</cfquery>
			
				</cfloop>
				
				<cfcatch type="any">
				
					<cftransaction action="rollback" />
					
					<cfthrow message="#cfcatch.Message#" detail="#cfcatch.Detail#" />
				
				</cfcatch>
			
			</cftry>
			
		</cftransaction>
			
	</cffunction>
	
</cfcomponent>
